﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using SalesModel;
using System.Data.SqlClient;


namespace SalesController
{
    public class SQLServerDAOPriceByCriteria : DAOPriceByCriteria
    {
        public void add(PriceByCriteria pc)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = "Server = inti.lab.inf.pucp.edu.pe;" + "Database = inf282, User ID = inf282; Password = inf282db;";
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO BoolPriceXCriterio(id_prod,metric_unit,quantity,price)" +
                    "VALUES (@param1,@param2,@param3,@param4)";
                SqlParameter param1 = new SqlParameter("@param1", System.Data.SqlDbType.Int);
                SqlParameter param2 = new SqlParameter("@param2", System.Data.SqlDbType.VarChar);
                SqlParameter param3 = new SqlParameter("@param3", System.Data.SqlDbType.Float);
                SqlParameter param4 = new SqlParameter("@param4", System.Data.SqlDbType.Float);

                param1.Value = pc.getIdProd();
                param2.Value = pc.getMetricUnit();
                param3.Value = pc.getQuantity();
                param4.Value = pc.getPrice();

                comm.Parameters.Add(param1);
                comm.Parameters.Add(param2);
                comm.Parameters.Add(param3);
                comm.Parameters.Add(param4);

                comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block			
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }
        }
        public void update(PriceByCriteria pc)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = "Server = inti.lab.inf.pucp.edu.pe;" + "Database = inf282, User ID = inf282; Password = inf282db;";
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE BoolPriceXCriterio SET metric_unit=@param1, quantity=@param2, price=@param3 WHERE id_prod=@param4";
                SqlParameter param1 = new SqlParameter("@param1", System.Data.SqlDbType.VarChar);
                SqlParameter param2 = new SqlParameter("@param2", System.Data.SqlDbType.Float);
                SqlParameter param3 = new SqlParameter("@param3", System.Data.SqlDbType.Float);
                SqlParameter param4 = new SqlParameter("@param4", System.Data.SqlDbType.Int);

                param1.Value = pc.getMetricUnit();
                param2.Value = pc.getQuantity();
                param3.Value = pc.getPrice();
                param4.Value = pc.getIdProd();

                comm.Parameters.Add(param1);
                comm.Parameters.Add(param2);
                comm.Parameters.Add(param3);
                comm.Parameters.Add(param4);

                comm.ExecuteNonQuery();
        }catch (Exception e)
            {
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }
        } 
        public void delete(int idProduct)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" + "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM BoolPriceXCriterio WHERE id_prod=@param1";
                SqlParameter param1 = new SqlParameter("@param1", System.Data.SqlDbType.Int);
                param1.Value = idProduct;
                comm.Parameters.Add(param1);
                comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }
        }
        public List<PriceByCriteria> queryAll()
        {
            List<PriceByCriteria> list = new List<PriceByCriteria>();
            SqlConnection conn = null;
            SqlDataReader myReader = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" + "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM BoolPriceXCriterio";
                myReader = comm.ExecuteReader();

                while (myReader.Read())
                {
                    PriceByCriteria p = new PriceByCriteria();
                    p.setIdProd(Int32.Parse(myReader["id_prod"].ToString()));
                    p.setPrice(double.Parse(myReader["price"].ToString()));
                    p.setQuantity(double.Parse(myReader["quantity"].ToString()));
                    p.setMetricUnit(myReader["metric_unit"].ToString());
                    list.Add(p);
                }

            }
            catch (Exception e)
            {
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                try { if (myReader != null) myReader.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }
            return list;
        }
        public PriceByCriteria queryByIdProduct(int idProd)
        {
            PriceByCriteria pd = null;
            SqlConnection conn = null;
            SqlDataReader myReader = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" + "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM BoolPriceByDate WHERE id=@param1";
                SqlParameter param1 = new SqlParameter("@param1", System.Data.SqlDbType.Int);
                param1.Value = idProd;
                comm.Parameters.Add(param1);
                myReader = comm.ExecuteReader();

                if (myReader.Read())
                {
                    pd = new PriceByCriteria();
                    pd.setIdProd(Int32.Parse(myReader["id_prod"].ToString()));
                    pd.setMetricUnit(myReader["metric_unit"].ToString());
                    pd.setPrice(Double.Parse(myReader["price"].ToString()));
                    pd.setQuantity(Double.Parse(myReader["quantity"].ToString()));
                }

            }
            catch (Exception e)
            {
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                try { if (myReader != null) myReader.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }
            return pd;
        }
        public int count()
        {
            int quantity = 0;
            SqlConnection conn = null;
            SqlDataReader myReader = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                    "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT Count(1) AS quantity FROM BoolPriceXCriterio";
                myReader = comm.ExecuteReader();
                if (myReader.Read())
                {
                    quantity = Int32.Parse(myReader["quantity"].ToString());
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                try { if (myReader != null) myReader.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }
            return quantity;
        }
    }
}
